{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ec4af0b8-9b05-4304-ba19-7cf39174fb35",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from perspective.widget import PerspectiveWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "358ee12d-527e-45d8-958a-96c5c11edbba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pl.read_parquet(\"stock_trades.parquet\")\n",
    "df = df.sort(\"交易日期\", \"交易时间\", \"证券代码\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "b1ed33b4-c2b6-4619-afde-0a0313a0907f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.with_columns(\n",
    "    pl.col(\"交易时间\").cast(pl.String),\n",
    "    手续费率=pl.col(\"手续费\") / pl.col(\"成交金额\"),\n",
    "    印花税率=pl.col(\"印花税\") / pl.col(\"成交金额\"),\n",
    "    过户费率=pl.col(\"过户费\") / pl.col(\"成交金额\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "64d5e61b-eb16-4c7b-a2fe-21dd55c5585e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.with_row_index(\"序号\", 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "438a3e83-3e04-4b93-b7fb-6e42527c045d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "70d69634-8b62-4c73-a298-065d3f01df4e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f6692e93e28449d0815f0a400f3046aa",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "72c60dbd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f6692e93e28449d0815f0a400f3046aa",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "5f67829d-e328-4f01-a481-46a50402afc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = df.join(\n",
    "    df.group_by(\"证券代码\", \"证券名称\")\n",
    "    .agg(\n",
    "        结余数量=pl.when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "        .then(-pl.col(\"成交数量\"))\n",
    "        .when(pl.col(\"买卖标志\") == \"买入\")\n",
    "        .then(pl.col(\"成交数量\"))\n",
    "        .sum()\n",
    "    )\n",
    "    .filter(pl.col(\"结余数量\") < 0),\n",
    "    on=\"证券代码\",\n",
    "    how=\"anti\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "9b0270a5-20e1-4c34-85ef-17f82d0b4092",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_date = df[\"交易日期\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "5ae2a756-faf5-4eb7-9bb5-1a204b9c6e4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "end_date = df[\"交易日期\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "232bc564-3509-47f9-a07e-fcbdaec0c578",
   "metadata": {},
   "outputs": [],
   "source": [
    "k1 = pl.select(日期=pl.date_range(start_date, end_date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "5a09b16a-8519-43cc-84d9-81776eb44bff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (152, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th></tr><tr><td>str</td></tr></thead><tbody><tr><td>&quot;000096&quot;</td></tr><tr><td>&quot;000532&quot;</td></tr><tr><td>&quot;000559&quot;</td></tr><tr><td>&quot;000599&quot;</td></tr><tr><td>&quot;000655&quot;</td></tr><tr><td>&hellip;</td></tr><tr><td>&quot;688299&quot;</td></tr><tr><td>&quot;688321&quot;</td></tr><tr><td>&quot;688360&quot;</td></tr><tr><td>&quot;688393&quot;</td></tr><tr><td>&quot;688660&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (152, 1)\n",
       "┌──────────┐\n",
       "│ 证券代码 │\n",
       "│ ---      │\n",
       "│ str      │\n",
       "╞══════════╡\n",
       "│ 000096   │\n",
       "│ 000532   │\n",
       "│ 000559   │\n",
       "│ 000599   │\n",
       "│ 000655   │\n",
       "│ …        │\n",
       "│ 688299   │\n",
       "│ 688321   │\n",
       "│ 688360   │\n",
       "│ 688393   │\n",
       "│ 688660   │\n",
       "└──────────┘"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k2 = df[\"证券代码\"].unique().sort().to_frame()\n",
    "k2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "a2880842-ebbf-4bb7-b5ab-eca389873aee",
   "metadata": {},
   "outputs": [],
   "source": [
    "k = k1.join(k2, how=\"cross\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "922c9f44-f710-4e1c-8761-33a887e5b927",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (4_489, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000900&quot;</td><td>1</td><td>&quot;湘财&quot;</td><td>&quot;09:33:37&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>34400.0</td></tr><tr><td>2022-07-11</td><td>&quot;002462&quot;</td><td>5</td><td>&quot;湘财&quot;</td><td>&quot;09:38:16&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>10400.0</td></tr><tr><td>2022-07-11</td><td>&quot;600894&quot;</td><td>3</td><td>&quot;湘财&quot;</td><td>&quot;09:36:30&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>21400.0</td></tr><tr><td>2022-07-11</td><td>&quot;601077&quot;</td><td>2</td><td>&quot;湘财&quot;</td><td>&quot;09:34:24&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>38300.0</td></tr><tr><td>2022-07-11</td><td>&quot;601992&quot;</td><td>4</td><td>&quot;湘财&quot;</td><td>&quot;09:37:25&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>54000.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;300132&quot;</td><td>361</td><td>&quot;海通两融&quot;</td><td>&quot;09:40:55&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td><td>9600.0</td></tr><tr><td>2023-10-31</td><td>&quot;300215&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20000.0</td></tr><tr><td>2023-10-31</td><td>&quot;600525&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>8500.0</td></tr><tr><td>2023-10-31</td><td>&quot;603214&quot;</td><td>360</td><td>&quot;海通两融&quot;</td><td>&quot;09:39:57&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td><td>3100.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (4_489, 19)\n",
       "┌────────────┬──────────┬──────┬──────────┬───┬──────────┬──────────┬──────────┬──────────┐\n",
       "│ 日期       ┆ 证券代码 ┆ 序号 ┆ 券商     ┆ … ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 ┆ 结余数量 │\n",
       "│ ---        ┆ ---      ┆ ---  ┆ ---      ┆   ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ date       ┆ str      ┆ u32  ┆ str      ┆   ┆ f64      ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞════════════╪══════════╪══════╪══════════╪═══╪══════════╪══════════╪══════════╪══════════╡\n",
       "│ 2022-07-11 ┆ 000900   ┆ 1    ┆ 湘财     ┆ … ┆ 0.00016  ┆ 0.0      ┆ 0.00001  ┆ 34400.0  │\n",
       "│ 2022-07-11 ┆ 002462   ┆ 5    ┆ 湘财     ┆ … ┆ 0.00016  ┆ 0.0      ┆ 0.00001  ┆ 10400.0  │\n",
       "│ 2022-07-11 ┆ 600894   ┆ 3    ┆ 湘财     ┆ … ┆ 0.00016  ┆ 0.0      ┆ 0.00001  ┆ 21400.0  │\n",
       "│ 2022-07-11 ┆ 601077   ┆ 2    ┆ 湘财     ┆ … ┆ 0.00016  ┆ 0.0      ┆ 0.00001  ┆ 38300.0  │\n",
       "│ 2022-07-11 ┆ 601992   ┆ 4    ┆ 湘财     ┆ … ┆ 0.00016  ┆ 0.0      ┆ 0.00001  ┆ 54000.0  │\n",
       "│ …          ┆ …        ┆ …    ┆ …        ┆ … ┆ …        ┆ …        ┆ …        ┆ …        │\n",
       "│ 2023-10-31 ┆ 300132   ┆ 361  ┆ 海通两融 ┆ … ┆ 0.0001   ┆ 0.0      ┆ 0.0      ┆ 9600.0   │\n",
       "│ 2023-10-31 ┆ 300215   ┆ null ┆ null     ┆ … ┆ null     ┆ null     ┆ null     ┆ 20000.0  │\n",
       "│ 2023-10-31 ┆ 600525   ┆ null ┆ null     ┆ … ┆ null     ┆ null     ┆ null     ┆ 8500.0   │\n",
       "│ 2023-10-31 ┆ 603214   ┆ 360  ┆ 海通两融 ┆ … ┆ 0.000102 ┆ 0.0      ┆ 0.00001  ┆ 3100.0   │\n",
       "│ 2023-10-31 ┆ 688660   ┆ null ┆ null     ┆ … ┆ null     ┆ null     ┆ null     ┆ 20200.0  │\n",
       "└────────────┴──────────┴──────┴──────────┴───┴──────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2 = (\n",
    "    # 使用left join合并两个DataFrame，通过日期和证券代码匹配\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    # 按日期和证券代码排序\n",
    "    .sort([\"日期\", \"证券代码\"])\n",
    "    # 添加一个新列\"结余数量\"，计算每个证券的累计持仓\n",
    "    .with_columns(\n",
    "        结余数量=pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "        .then(pl.col(\"成交数量\"))\n",
    "        .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "        .then(-pl.col(\"成交数量\"))\n",
    "        .otherwise(0)\n",
    "        .cum_sum()\n",
    "        .over(\"证券代码\")\n",
    "    )\n",
    "    # 筛选出结余数量大于0的记录\n",
    ").filter(pl.col(\"结余数量\") > 0)\n",
    "d2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "ab33936b-c5e1-4c63-89b0-114f67bd2be4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f8d7ddf059fb4092be1c33e9eaa75d25",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '证券代码', '序号', '券商', '交易时间', '证券名称', '买卖标志', '成交价格', '成…"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "370b6956-5454-4ad7-b8c8-3cf11bc1a8cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "\n",
    "pro = ts.pro_api()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "4204c200-82bc-4437-a1b9-28fca32a2930",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20220711'"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"{start_date:%Y%m%d}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "92f458ab-2be9-4a87-b7ec-a0552e4ba5fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20231031'"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "format(end_date, \"%Y%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "3fdfbf3d-678e-4b21-af77-9ad41a115f34",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pro.daily(\n",
    "    ts_code=\"002462.SZ\",\n",
    "    start_date=format(start_date, \"%Y%m%d\"),\n",
    "    end_date=format(end_date, \"%Y%m%d\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "1d82363e-e3bc-44d8-bc4c-be33bbf69b0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231031&quot;</td><td>14.75</td><td>14.9</td><td>14.59</td><td>14.7</td><td>14.75</td><td>-0.05</td><td>-0.339</td><td>65859.96</td><td>96984.271</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231030&quot;</td><td>13.88</td><td>14.89</td><td>13.88</td><td>14.75</td><td>13.93</td><td>0.82</td><td>5.8866</td><td>123932.16</td><td>180119.372</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231027&quot;</td><td>13.7</td><td>13.98</td><td>13.51</td><td>13.93</td><td>13.64</td><td>0.29</td><td>2.1261</td><td>35782.0</td><td>49386.168</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231026&quot;</td><td>13.49</td><td>13.68</td><td>13.4</td><td>13.64</td><td>13.62</td><td>0.02</td><td>0.1468</td><td>19215.0</td><td>26005.866</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231025&quot;</td><td>13.65</td><td>13.77</td><td>13.58</td><td>13.62</td><td>13.67</td><td>-0.05</td><td>-0.3658</td><td>18484.0</td><td>25274.163</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220715&quot;</td><td>13.61</td><td>13.66</td><td>13.12</td><td>13.13</td><td>13.59</td><td>-0.46</td><td>-3.3848</td><td>32967.65</td><td>44114.064</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220714&quot;</td><td>13.54</td><td>13.75</td><td>13.5</td><td>13.59</td><td>13.54</td><td>0.05</td><td>0.3693</td><td>21967.0</td><td>29851.164</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220713&quot;</td><td>13.55</td><td>13.63</td><td>13.39</td><td>13.54</td><td>13.61</td><td>-0.07</td><td>-0.5143</td><td>22793.0</td><td>30714.624</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220712&quot;</td><td>13.65</td><td>13.69</td><td>13.41</td><td>13.61</td><td>13.65</td><td>-0.04</td><td>-0.293</td><td>29679.0</td><td>40146.31</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220711&quot;</td><td>13.2</td><td>13.96</td><td>13.07</td><td>13.65</td><td>13.18</td><td>0.47</td><td>3.566</td><td>62827.0</td><td>85869.11</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 11)\n",
       "┌───────────┬────────────┬───────┬───────┬───┬────────┬─────────┬───────────┬────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ open  ┆ high  ┆ … ┆ change ┆ pct_chg ┆ vol       ┆ amount     │\n",
       "│ ---       ┆ ---        ┆ ---   ┆ ---   ┆   ┆ ---    ┆ ---     ┆ ---       ┆ ---        │\n",
       "│ str       ┆ str        ┆ f64   ┆ f64   ┆   ┆ f64    ┆ f64     ┆ f64       ┆ f64        │\n",
       "╞═══════════╪════════════╪═══════╪═══════╪═══╪════════╪═════════╪═══════════╪════════════╡\n",
       "│ 002462.SZ ┆ 20231031   ┆ 14.75 ┆ 14.9  ┆ … ┆ -0.05  ┆ -0.339  ┆ 65859.96  ┆ 96984.271  │\n",
       "│ 002462.SZ ┆ 20231030   ┆ 13.88 ┆ 14.89 ┆ … ┆ 0.82   ┆ 5.8866  ┆ 123932.16 ┆ 180119.372 │\n",
       "│ 002462.SZ ┆ 20231027   ┆ 13.7  ┆ 13.98 ┆ … ┆ 0.29   ┆ 2.1261  ┆ 35782.0   ┆ 49386.168  │\n",
       "│ 002462.SZ ┆ 20231026   ┆ 13.49 ┆ 13.68 ┆ … ┆ 0.02   ┆ 0.1468  ┆ 19215.0   ┆ 26005.866  │\n",
       "│ 002462.SZ ┆ 20231025   ┆ 13.65 ┆ 13.77 ┆ … ┆ -0.05  ┆ -0.3658 ┆ 18484.0   ┆ 25274.163  │\n",
       "│ …         ┆ …          ┆ …     ┆ …     ┆ … ┆ …      ┆ …       ┆ …         ┆ …          │\n",
       "│ 002462.SZ ┆ 20220715   ┆ 13.61 ┆ 13.66 ┆ … ┆ -0.46  ┆ -3.3848 ┆ 32967.65  ┆ 44114.064  │\n",
       "│ 002462.SZ ┆ 20220714   ┆ 13.54 ┆ 13.75 ┆ … ┆ 0.05   ┆ 0.3693  ┆ 21967.0   ┆ 29851.164  │\n",
       "│ 002462.SZ ┆ 20220713   ┆ 13.55 ┆ 13.63 ┆ … ┆ -0.07  ┆ -0.5143 ┆ 22793.0   ┆ 30714.624  │\n",
       "│ 002462.SZ ┆ 20220712   ┆ 13.65 ┆ 13.69 ┆ … ┆ -0.04  ┆ -0.293  ┆ 29679.0   ┆ 40146.31   │\n",
       "│ 002462.SZ ┆ 20220711   ┆ 13.2  ┆ 13.96 ┆ … ┆ 0.47   ┆ 3.566   ┆ 62827.0   ┆ 85869.11   │\n",
       "└───────────┴────────────┴───────┴───────┴───┴────────┴─────────┴───────────┴────────────┘"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq = pl.from_pandas(hq)\n",
    "hq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "d8126ed8-fa6e-4a04-b727-e83030408400",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_codes = (\n",
    "    d1.select(\n",
    "        证券代码=(\n",
    "            # 检查证券代码的首字符\n",
    "            pl.when(pl.col(\"证券代码\").str.head(1).is_in([\"0\", \"3\"]))\n",
    "            # 深交所代码（0开头和3开头）添加.SZ后缀\n",
    "            .then(pl.format(\"{}.SZ\", pl.col(\"证券代码\")))\n",
    "            # 上交所代码（6开头）添加.Sh后缀\n",
    "            .when(pl.col(\"证券代码\").str.head(1) == \"6\")\n",
    "            .then(pl.format(\"{}.Sh\", pl.col(\"证券代码\")))\n",
    "            # 对于不符合条件的代码（如首字符不是0/3/6），返回null\n",
    "        ),\n",
    "    )\n",
    "    .to_series()\n",
    "    .unique()\n",
    "    .sort()\n",
    "    .to_list()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "82f89e2c-cba0-46b2-ae5b-ea97faf0cd9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "7597a811-3c51-4e88-8017-59c0cf3b501b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "beb593d7f0734eb29dcce1aae1338068",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/149 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hq = [\n",
    "    pl.from_pandas(\n",
    "        pro.daily(\n",
    "            ts_code=ts_code,\n",
    "            start_date=format(start_date, \"%Y%m%d\"),\n",
    "            end_date=format(end_date, \"%Y%m%d\"),\n",
    "        )\n",
    "    )\n",
    "    for ts_code in tqdm(ts_codes)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "725c188e-796f-41bc-ab3a-a81520eb2494",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.concat(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "8f86595d-3c02-44f1-98e1-a028b841aacc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/c/Users/13212/repo/week08\n"
     ]
    }
   ],
   "source": [
    "!pwd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "75e43cf9-d811-4878-8be5-d52c04c312cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq.write_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "1a771fd7-cd7c-4120-8ed6-c10a207450f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.read_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "a44825b7-b048-4f15-b21a-49093fd2806e",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = hq.with_columns(\n",
    "    pl.col(\"ts_code\").str.head(6),\n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "709fc03b-04f9-4bab-8d6f-ed1a678c771b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (0, 27)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (0, 27)\n",
       "┌──────┬──────┬──────────┬──────────┬───┬────────┬─────────┬─────┬────────┐\n",
       "│ 序号 ┆ 券商 ┆ 交易日期 ┆ 交易时间 ┆ … ┆ change ┆ pct_chg ┆ vol ┆ amount │\n",
       "│ ---  ┆ ---  ┆ ---      ┆ ---      ┆   ┆ ---    ┆ ---     ┆ --- ┆ ---    │\n",
       "│ u32  ┆ str  ┆ date     ┆ str      ┆   ┆ f64    ┆ f64     ┆ f64 ┆ f64    │\n",
       "╞══════╪══════╪══════════╪══════════╪═══╪════════╪═════════╪═════╪════════╡\n",
       "└──────┴──────┴──────────┴──────────┴───┴────────┴─────────┴─────┴────────┘"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1.join(\n",
    "    hq, left_on=[\"交易日期\", \"证券代码\"], right_on=[\"trade_date\", \"ts_code\"], how=\"left\"\n",
    ").filter(\n",
    "    ~pl.col(\"成交价格\").is_between(pl.col(\"low\"), pl.col(\"high\")),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "ee156cb8-fdef-47d8-bbaa-8042dabebe66",
   "metadata": {},
   "outputs": [],
   "source": [
    "d3 = (\n",
    "    # 使用left join合并两个DataFrame，通过日期和证券代码匹配\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    # 按日期和证券代码排序\n",
    "    .sort([\"日期\", \"证券代码\"])\n",
    "    # 添加一个新列\"结余数量\"，计算每个证券的累计持仓\n",
    "    .with_columns(\n",
    "        结余数量=pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "        .then(pl.col(\"成交数量\"))\n",
    "        .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "        .then(-pl.col(\"成交数量\"))\n",
    "        .otherwise(0)\n",
    "        .cum_sum()\n",
    "        .over(\"证券代码\")\n",
    "    )\n",
    "    # 筛选出结余数量大于0的记录\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "9d7d11d7-b550-47e3-8514-f433ef88237a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (72_671, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000096&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000532&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000559&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000599&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000655&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;688299&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688321&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688360&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688393&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (72_671, 19)\n",
       "┌────────────┬──────────┬──────┬──────┬───┬──────────┬──────────┬──────────┬──────────┐\n",
       "│ 日期       ┆ 证券代码 ┆ 序号 ┆ 券商 ┆ … ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 ┆ 结余数量 │\n",
       "│ ---        ┆ ---      ┆ ---  ┆ ---  ┆   ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ date       ┆ str      ┆ u32  ┆ str  ┆   ┆ f64      ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞════════════╪══════════╪══════╪══════╪═══╪══════════╪══════════╪══════════╪══════════╡\n",
       "│ 2022-07-11 ┆ 000096   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000532   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000559   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000599   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000655   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ …          ┆ …        ┆ …    ┆ …    ┆ … ┆ …        ┆ …        ┆ …        ┆ …        │\n",
       "│ 2023-10-31 ┆ 688299   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688321   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688360   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688393   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688660   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 20200.0  │\n",
       "└────────────┴──────────┴──────┴──────┴───┴──────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "ce3fe0a4-7f27-45b6-ab50-79f1c084551c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (478, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>持股市值总和</th><th>发生金额总和</th><th>转账金额</th><th>现金余额</th></tr><tr><td>date</td><td>f64</td><td>f64</td><td>i32</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>703040.0</td><td>-699551.12</td><td>1000000</td><td>300448.88</td></tr><tr><td>2022-07-12</td><td>707714.0</td><td>0.0</td><td>0</td><td>300448.88</td></tr><tr><td>2022-07-13</td><td>713855.0</td><td>0.0</td><td>0</td><td>300448.88</td></tr><tr><td>2022-07-14</td><td>710079.0</td><td>0.0</td><td>0</td><td>300448.88</td></tr><tr><td>2022-07-15</td><td>692377.0</td><td>0.0</td><td>0</td><td>300448.88</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-27</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td></tr><tr><td>2023-10-28</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td></tr><tr><td>2023-10-29</td><td>571195.0</td><td>0.0</td><td>0</td><td>510845.96</td></tr><tr><td>2023-10-30</td><td>686345.0</td><td>-94884.93</td><td>0</td><td>415961.03</td></tr><tr><td>2023-10-31</td><td>748947.0</td><td>6490.78</td><td>0</td><td>422451.81</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (478, 5)\n",
       "┌────────────┬──────────────┬──────────────┬──────────┬───────────┐\n",
       "│ 日期       ┆ 持股市值总和 ┆ 发生金额总和 ┆ 转账金额 ┆ 现金余额  │\n",
       "│ ---        ┆ ---          ┆ ---          ┆ ---      ┆ ---       │\n",
       "│ date       ┆ f64          ┆ f64          ┆ i32      ┆ f64       │\n",
       "╞════════════╪══════════════╪══════════════╪══════════╪═══════════╡\n",
       "│ 2022-07-11 ┆ 703040.0     ┆ -699551.12   ┆ 1000000  ┆ 300448.88 │\n",
       "│ 2022-07-12 ┆ 707714.0     ┆ 0.0          ┆ 0        ┆ 300448.88 │\n",
       "│ 2022-07-13 ┆ 713855.0     ┆ 0.0          ┆ 0        ┆ 300448.88 │\n",
       "│ 2022-07-14 ┆ 710079.0     ┆ 0.0          ┆ 0        ┆ 300448.88 │\n",
       "│ 2022-07-15 ┆ 692377.0     ┆ 0.0          ┆ 0        ┆ 300448.88 │\n",
       "│ …          ┆ …            ┆ …            ┆ …        ┆ …         │\n",
       "│ 2023-10-27 ┆ 571195.0     ┆ 0.0          ┆ 0        ┆ 510845.96 │\n",
       "│ 2023-10-28 ┆ 571195.0     ┆ 0.0          ┆ 0        ┆ 510845.96 │\n",
       "│ 2023-10-29 ┆ 571195.0     ┆ 0.0          ┆ 0        ┆ 510845.96 │\n",
       "│ 2023-10-30 ┆ 686345.0     ┆ -94884.93    ┆ 0        ┆ 415961.03 │\n",
       "│ 2023-10-31 ┆ 748947.0     ┆ 6490.78      ┆ 0        ┆ 422451.81 │\n",
       "└────────────┴──────────────┴──────────────┴──────────┴───────────┘"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d4 = (\n",
    "    d3.join(\n",
    "        hq,\n",
    "        left_on=[\"日期\", \"证券代码\"],\n",
    "        right_on=[\"trade_date\", \"ts_code\"],\n",
    "        how=\"left\",\n",
    "    )\n",
    "    .sort(\"证券代码\", \"日期\")\n",
    "    .with_columns(close=pl.col(\"close\").fill_null(strategy=\"forward\").over(\"证券代码\"))\n",
    "    .with_columns(持股市值=pl.col(\"结余数量\") * pl.col(\"close\"))\n",
    "    .group_by(\"日期\")\n",
    "    .agg(\n",
    "        pl.col(\"持股市值\").sum().alias(\"持股市值总和\"),\n",
    "        pl.col(\"发生金额\").sum().alias(\"发生金额总和\"),\n",
    "    )\n",
    "    .sort(\"日期\")\n",
    "    .with_columns(\n",
    "        转账金额=pl.when(pl.int_range(0, pl.len()) == 0).then(1000000).otherwise(0)\n",
    "    )\n",
    "    # 修改这里，使用聚合后的列名\n",
    "    .with_columns(\n",
    "        现金余额=(pl.col(\"转账金额\") + pl.col(\"发生金额总和\")).cum_sum(),\n",
    "    )\n",
    ")\n",
    "d4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "b80bfba8-f59b-4d18-9dde-a71c2418455f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9079b9841651405987490b00681d4e61",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '持股市值总和', '发生金额总和', '转账金额', '现金余额'], table_name='0.413…"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "99ac9fdd-bc2c-4a2a-b53c-89972d3b5107",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "0def9fdf-e5db-45a3-8364-81ff662f67f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "ihq = pro.index_daily(\n",
    "    ts_code=\"000300.SH\",\n",
    "    start_date=format(start_date, \"%Y%M%d\"),\n",
    "    end_date=format(end_date, \"%Y%M%d\"),\n",
    "    fildes=\"ts_code,trade_date,pct_chg\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "e0a965e2-0eb6-4730-a435-a04934e2a9e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "pl.from_pandas(ihq).write_parquet(\"index_daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "bec981e8-a716-4601-af73-c1e90f9e4a5a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (242, 13)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>close</th><th>open</th><th>high</th><th>low</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th><th>car</th><th>沪深300</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;000300.SH&quot;</td><td>&quot;20220104&quot;</td><td>4917.7653</td><td>4957.9835</td><td>4961.4527</td><td>4874.5337</td><td>4940.3733</td><td>-22.608</td><td>0.995424</td><td>1.51534776e8</td><td>3.3652e8</td><td>0.995424</td><td>995424.0</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20220105&quot;</td><td>4868.1202</td><td>4907.9347</td><td>4916.2822</td><td>4851.9835</td><td>4917.7653</td><td>-49.6451</td><td>0.989905</td><td>1.788161e8</td><td>3.6394e8</td><td>0.985375</td><td>985375.19472</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20220106&quot;</td><td>4818.2318</td><td>4842.156</td><td>4857.5596</td><td>4786.433</td><td>4868.1202</td><td>-49.8884</td><td>0.989752</td><td>1.57665826e8</td><td>3.2175e8</td><td>0.975277</td><td>975277.069725</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20220107&quot;</td><td>4822.3689</td><td>4824.3181</td><td>4856.6522</td><td>4818.1931</td><td>4818.2318</td><td>4.1371</td><td>1.000859</td><td>1.87139413e8</td><td>3.3222e8</td><td>0.976115</td><td>976114.832727</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20220110&quot;</td><td>4844.0451</td><td>4812.23</td><td>4844.3928</td><td>4780.8191</td><td>4822.3689</td><td>21.6762</td><td>1.004495</td><td>1.56211696e8</td><td>3.0503e8</td><td>0.980502</td><td>980502.468901</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20221226&quot;</td><td>3843.4887</td><td>3830.5682</td><td>3856.8066</td><td>3827.5186</td><td>3828.2188</td><td>15.2699</td><td>1.003989</td><td>7.3005712e7</td><td>1.4869e8</td><td>0.777973</td><td>777973.402172</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20221227&quot;</td><td>3887.854</td><td>3860.9054</td><td>3891.8497</td><td>3856.58</td><td>3843.4887</td><td>44.3653</td><td>1.011543</td><td>7.751535e7</td><td>1.5203e8</td><td>0.786954</td><td>786953.549153</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20221228&quot;</td><td>3871.2644</td><td>3874.9132</td><td>3884.7205</td><td>3854.9406</td><td>3887.854</td><td>-16.5896</td><td>0.995733</td><td>8.4424656e7</td><td>1.6067e8</td><td>0.783596</td><td>783595.618359</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20221229&quot;</td><td>3856.7007</td><td>3854.2363</td><td>3868.2933</td><td>3838.4803</td><td>3871.2644</td><td>-14.5637</td><td>0.996238</td><td>7.9394815e7</td><td>1.4901e8</td><td>0.780648</td><td>780647.731643</td></tr><tr><td>&quot;000300.SH&quot;</td><td>&quot;20221230&quot;</td><td>3871.6338</td><td>3873.9358</td><td>3887.5527</td><td>3868.7569</td><td>3856.7007</td><td>14.9331</td><td>1.003872</td><td>7.8773272e7</td><td>1.4830e8</td><td>0.78367</td><td>783670.39966</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (242, 13)\n",
       "┌───────────┬────────────┬───────────┬───────────┬───┬──────────────┬──────────┬──────────┬───────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ close     ┆ open      ┆ … ┆ vol          ┆ amount   ┆ car      ┆ 沪深300       │\n",
       "│ ---       ┆ ---        ┆ ---       ┆ ---       ┆   ┆ ---          ┆ ---      ┆ ---      ┆ ---           │\n",
       "│ str       ┆ str        ┆ f64       ┆ f64       ┆   ┆ f64          ┆ f64      ┆ f64      ┆ f64           │\n",
       "╞═══════════╪════════════╪═══════════╪═══════════╪═══╪══════════════╪══════════╪══════════╪═══════════════╡\n",
       "│ 000300.SH ┆ 20220104   ┆ 4917.7653 ┆ 4957.9835 ┆ … ┆ 1.51534776e8 ┆ 3.3652e8 ┆ 0.995424 ┆ 995424.0      │\n",
       "│ 000300.SH ┆ 20220105   ┆ 4868.1202 ┆ 4907.9347 ┆ … ┆ 1.788161e8   ┆ 3.6394e8 ┆ 0.985375 ┆ 985375.19472  │\n",
       "│ 000300.SH ┆ 20220106   ┆ 4818.2318 ┆ 4842.156  ┆ … ┆ 1.57665826e8 ┆ 3.2175e8 ┆ 0.975277 ┆ 975277.069725 │\n",
       "│ 000300.SH ┆ 20220107   ┆ 4822.3689 ┆ 4824.3181 ┆ … ┆ 1.87139413e8 ┆ 3.3222e8 ┆ 0.976115 ┆ 976114.832727 │\n",
       "│ 000300.SH ┆ 20220110   ┆ 4844.0451 ┆ 4812.23   ┆ … ┆ 1.56211696e8 ┆ 3.0503e8 ┆ 0.980502 ┆ 980502.468901 │\n",
       "│ …         ┆ …          ┆ …         ┆ …         ┆ … ┆ …            ┆ …        ┆ …        ┆ …             │\n",
       "│ 000300.SH ┆ 20221226   ┆ 3843.4887 ┆ 3830.5682 ┆ … ┆ 7.3005712e7  ┆ 1.4869e8 ┆ 0.777973 ┆ 777973.402172 │\n",
       "│ 000300.SH ┆ 20221227   ┆ 3887.854  ┆ 3860.9054 ┆ … ┆ 7.751535e7   ┆ 1.5203e8 ┆ 0.786954 ┆ 786953.549153 │\n",
       "│ 000300.SH ┆ 20221228   ┆ 3871.2644 ┆ 3874.9132 ┆ … ┆ 8.4424656e7  ┆ 1.6067e8 ┆ 0.783596 ┆ 783595.618359 │\n",
       "│ 000300.SH ┆ 20221229   ┆ 3856.7007 ┆ 3854.2363 ┆ … ┆ 7.9394815e7  ┆ 1.4901e8 ┆ 0.780648 ┆ 780647.731643 │\n",
       "│ 000300.SH ┆ 20221230   ┆ 3871.6338 ┆ 3873.9358 ┆ … ┆ 7.8773272e7  ┆ 1.4830e8 ┆ 0.78367  ┆ 783670.39966  │\n",
       "└───────────┴────────────┴───────────┴───────────┴───┴──────────────┴──────────┴──────────┴───────────────┘"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ihq = pl.read_parquet(\"index_daily.parquet\")\n",
    "ihq = (\n",
    "    ihq.with_columns(\n",
    "        pl.col(\"pct_chg\") / 100 + 1,\n",
    "    )\n",
    "    .sort(\"trade_date\")\n",
    "    .with_columns(\n",
    "        car=pl.col(\"pct_chg\").cum_prod(),\n",
    "    )\n",
    "    .with_columns(沪深300=pl.col(\"car\") * 1000000)\n",
    ")\n",
    "ihq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "a9f37a12-13d7-4cc7-9c06-8ffcdfc6f02c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "83c5b57ae4dd44d783c772ea7e3b0bda",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['ts_code', 'trade_date', 'close', 'open', 'high', 'low', 'pr…"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(ihq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3c6a4fa-ba1a-4ce3-a24a-945ffc16d040",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
